using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using Metro.Admin.BusinessEntities;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Transactions;

namespace Metro.Admin.DataAccess
{
    public partial class Transaction
    {
        Database metroDb = DatabaseFactory.CreateDatabase("metroDB");

        public bool ExistsTransaction(Metro.Admin.BusinessEntities.Transaction transaction)
        {
            Guid idOrder = new  Guid(transaction.OriginalOrder.Id);
            object retValue = null;
            bool retFlag = false;
            Guid idTransaction;

            try
            { 
                string sqlCommand = "SELECT [id] FROM  [Transaction]  WHERE id=@id";
                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                metroDb.AddInParameter(dbCommand, "@id", DbType.Guid, idOrder);
                retValue = metroDb.ExecuteScalar(dbCommand);

                if (retValue != null)
                {
                    retFlag = true;
                    idTransaction = (Guid)retValue;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return retFlag;
        }

        public PreTransactionSummary GetTransactionSummary(string idUser)
        {
            PreTransactionSummary preTransactionSummary = null;
            Guid idUserGuid = new Guid(idUser);

            try
            {
                preTransactionSummary = new PreTransactionSummary();

                string sqlCommand = "SELECT ISNULL(SUM([Transaction].amount * [Transaction].basePrice) / SUM([Transaction].amount), 0) AS " + "avgBuy, ISNULL(SUM([Transaction].amount), 0) AS totalBuy FROM [Transaction] INNER JOIN [Order] ON " +
                                    "[Transaction].idOrderPost = [Order].id GROUP BY [Order].businessType, [Order].idUser, [Transaction].state " + "HAVING ([Order].businessType = 'B') AND ([Order].idUser = @idUser) AND ([Transaction].state = 'A')";
                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                metroDb.AddInParameter(dbCommand, "@idUser", DbType.Guid, idUserGuid);

                using (IDataReader dataReader = metroDb.ExecuteReader(dbCommand))
                {
                    while (dataReader.Read())
                    {
                        preTransactionSummary.AvgBuy = Convert.ToDecimal(dataReader["avgBuy"].ToString());
                        preTransactionSummary.TotalBuy = Convert.ToDecimal(dataReader["totalBuy"].ToString());  
                    }
                }

                sqlCommand = "SELECT ISNULL(SUM([Transaction].amount * [Transaction].basePrice) / SUM([Transaction].amount), 0) AS " + "avgSell, ISNULL(SUM([Transaction].amount), 0) AS totalSell FROM [Transaction] INNER JOIN [Order] ON " +
                                    "[Transaction].idOrderPost = [Order].id GROUP BY [Order].businessType, [Order].idUser, [Transaction].state " + "HAVING ([Order].businessType = 'S') AND ([Order].idUser = @idUser) AND ([Transaction].state = 'A')";
                dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                metroDb.AddInParameter(dbCommand, "@idUser", DbType.Guid, idUserGuid);

                using (IDataReader dataReader = metroDb.ExecuteReader(dbCommand))
                {
                    while (dataReader.Read())
                    {
                        preTransactionSummary.AvgSell = Convert.ToDecimal(dataReader["avgSell"].ToString());
                        preTransactionSummary.TotalSell = Convert.ToDecimal(dataReader["totalSell"].ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                
                throw ex;
            }

            return preTransactionSummary;
        
        }

        public void AddTransaction(Metro.Admin.BusinessEntities.Transaction transaction)
        {
            Guid idTransaction = new Guid(transaction.Id);
            Guid idOrderPost = new Guid(transaction.IdOrderPost);
            Guid idOrderGet = new Guid(transaction.IdOrderGet);
            Guid idPair = new Guid(transaction.IdPair);
            Guid idCustomer = new Guid(transaction.OriginalOrder.CustomerId);
            Customer customer = null;
            AdminDataSource customerDA = new AdminDataSource();
            decimal futureTransitSellCredit = 0;
            decimal futureTransitBuyCredit = 0;
            decimal futureBuyCredit = 0;
            decimal futureSellCredit = 0;
            int sign = 0;
            decimal futurePosition = 0;
            decimal margin = 0;
            decimal minimalAmount = 0;
            PreTransactionSummary preTransactionSummary = null;
            decimal futureProfit = 0;

            try
            {
                if (!this.ExistsTransaction(transaction))
                {
                    customer = customerDA.GetCustomer(transaction.OriginalOrder.CustomerId);

                    if (transaction.OriginalOrder.OrderBusinessType.Equals(BusinessType.Buy))
                        sign = 1;
                    else if(transaction.OriginalOrder.OrderBusinessType.Equals(BusinessType.Sell))
                        sign = -1;

                        
                    using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
                    {
                        string sqlCommand = "INSERT INTO [Transaction] ([id],[idOrderPost],[idOrderGet],[date],[state],[amount],[basePrice],[commission],[tax],[baseTax],[taxTransaction],[type],[idPair])" +
        "VALUES (@id,@idOrderPost,@idOrderGet,@date,@state,@amount,@basePrice,@commission,@tax,@baseTax,@taxTransaction,@type,@idPair)";

                        DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);

                        metroDb.AddInParameter(dbCommand, "@id", DbType.Guid, idTransaction);
                        metroDb.AddInParameter(dbCommand, "@idOrderPost", DbType.Guid, idOrderPost);
                        metroDb.AddInParameter(dbCommand, "@idOrderGet", DbType.Guid, idOrderGet);
                        metroDb.AddInParameter(dbCommand, "@date", DbType.DateTime, transaction.Date);
                        metroDb.AddInParameter(dbCommand, "@state", DbType.String, OrderServices.OrderState2Str(transaction.TransactionState));
                        metroDb.AddInParameter(dbCommand, "@amount", DbType.Currency, transaction.Amount);
                        metroDb.AddInParameter(dbCommand, "@basePrice", DbType.Currency, transaction.BasePrice);
                        metroDb.AddInParameter(dbCommand, "@commission", DbType.Currency, transaction.Commission);
                        metroDb.AddInParameter(dbCommand, "@tax", DbType.Currency, transaction.Tax);
                        metroDb.AddInParameter(dbCommand, "@baseTax", DbType.Currency, transaction.BaseTax);
                        metroDb.AddInParameter(dbCommand, "@taxTransaction", DbType.Currency, transaction.TaxTransaction);
                        metroDb.AddInParameter(dbCommand, "@type", DbType.Int16, transaction.Type);
                        metroDb.AddInParameter(dbCommand, "@idPair", DbType.Guid, idPair);

                        metroDb.ExecuteNonQuery(dbCommand);

                        preTransactionSummary = this.GetTransactionSummary(transaction.OriginalOrder.UserId);  

                        futureTransitBuyCredit = customer.TransitBuyCredit - transaction.Amount;
                        futureTransitSellCredit = customer.TransitSellCredit - transaction.Amount;
                        futureBuyCredit = customer.BuyCredit - (transaction.Amount*sign);
                        futureSellCredit = customer.SellCredit + (transaction.Amount*sign);
                        futurePosition = customer.Position + (transaction.Amount*sign);
                        margin = preTransactionSummary.AvgSell - preTransactionSummary.AvgBuy;
                        

                        if (preTransactionSummary.TotalBuy > 0 & preTransactionSummary.TotalSell > 0)
                        {
                            if (preTransactionSummary.TotalBuy < preTransactionSummary.TotalSell)
                                minimalAmount = preTransactionSummary.TotalBuy;
                            else
                                minimalAmount = preTransactionSummary.TotalSell;
                        }

                        futureProfit = margin * minimalAmount; 

                        if (customer.CustomerType.Equals(CustomerType.OPCF))
                        {

                            sqlCommand = "UPDATE Customer SET transitBuyCredit = @transitBuyCredit, transitSellCredit = @transitSellCredit," + "buyCredit = @buyCredit, sellCredit = @sellCredit, position = @position, profit = @profit," +
                                         "avgBuy = @avgBuy, avgSell = @avgSell WHERE (idCustomer = @idCustomer)";

                            dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                            metroDb.AddInParameter(dbCommand, "@transitBuyCredit", DbType.Currency, futureTransitBuyCredit);
                            metroDb.AddInParameter(dbCommand, "@transitSellCredit", DbType.Currency, futureTransitSellCredit);
                            metroDb.AddInParameter(dbCommand, "@buyCredit", DbType.Currency, futureBuyCredit);
                            metroDb.AddInParameter(dbCommand, "@sellCredit", DbType.Currency, futureSellCredit);
                            metroDb.AddInParameter(dbCommand, "@position", DbType.Currency, futurePosition);
                            metroDb.AddInParameter(dbCommand, "@profit", DbType.Currency, futureProfit);
                            metroDb.AddInParameter(dbCommand, "@avgBuy", DbType.Currency, preTransactionSummary.AvgBuy);
                            metroDb.AddInParameter(dbCommand, "@avgSell", DbType.Currency, preTransactionSummary.AvgSell);
                            metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);

                            metroDb.ExecuteNonQuery(dbCommand);
                        }
                        else if (customer.CustomerType.Equals(CustomerType.Cambiario))
                        {
                            if (transaction.OriginalOrder.OrderBusinessType.Equals(BusinessType.Buy))
                            {
                                sqlCommand = "UPDATE Customer SET transitBuyCredit = @transitBuyCredit, buyCredit = @buyCredit " +
                                             "WHERE (idCustomer = @idCustomer)";
                                dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                                metroDb.AddInParameter(dbCommand, "@transitBuyCredit", DbType.Currency, futureTransitBuyCredit);
                                metroDb.AddInParameter(dbCommand, "@buyCredit", DbType.Currency, futureBuyCredit);
                                metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);
                                metroDb.ExecuteNonQuery(dbCommand);

                            }
                            else if (transaction.OriginalOrder.OrderBusinessType.Equals(BusinessType.Sell))
                            {
                                sqlCommand = "UPDATE Customer SET transitSellCredit = @transitSellCredit, sellCredit = @sellCredit " + 
                                             "WHERE (idCustomer = @idCustomer)";
                                dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                                metroDb.AddInParameter(dbCommand, "@transitSellCredit", DbType.Currency, futureTransitSellCredit);
                                metroDb.AddInParameter(dbCommand, "@sellCredit", DbType.Currency, futureSellCredit);
                                metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);
                                metroDb.ExecuteNonQuery(dbCommand);

                            }
                        }

                        sqlCommand = "MakeStats";
                        dbCommand = metroDb.GetStoredProcCommand(sqlCommand);
                        metroDb.AddInParameter(dbCommand, "@market", DbType.String, OrderServices.Market2Str (transaction.OriginalOrder.Market) );
                        metroDb.AddInParameter(dbCommand, "@date", DbType.DateTime, DateTime.Today);
                        metroDb.ExecuteNonQuery(dbCommand);

                        scope.Complete();  

                    }
                }


            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteTransaction(Metro.Admin.BusinessEntities.Transaction transaction)
        {
            Guid idTransaction = new Guid(transaction.Id);
            Guid idCustomer = new Guid(transaction.OriginalOrder.CustomerId);
            Customer customer = null;
            AdminDataSource customerDA = new AdminDataSource();
            decimal futureTransitSellCredit = 0;
            decimal futureTransitBuyCredit = 0;
            decimal futureBuyCredit = 0;
            decimal futureSellCredit = 0;
            int sign = 0;
            decimal futurePosition = 0;
            decimal margin = 0;
            decimal minimalAmount = 0;
            PreTransactionSummary preTransactionSummary = null;
            decimal futureProfit = 0;

            try
            {
                customer = customerDA.GetCustomer(transaction.OriginalOrder.CustomerId);

                if (transaction.OriginalOrder.OrderBusinessType.Equals(BusinessType.Buy))
                    sign = 1;
                else if (transaction.OriginalOrder.OrderBusinessType.Equals(BusinessType.Sell))
                    sign = -1;

                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
                {
                    string sqlCommand = "UPDATE [Transaction] SET [state] = @state WHERE [idPair] = @id";

                    DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);

                    metroDb.AddInParameter(dbCommand, "@id", DbType.Guid, idTransaction);

                    metroDb.AddInParameter(dbCommand, "@state", DbType.String, OrderServices.OrderState2Str(OrderState.Cancel));
                    metroDb.ExecuteNonQuery(dbCommand);

                    preTransactionSummary = this.GetTransactionSummary(transaction.OriginalOrder.UserId);
                    
                    futureTransitBuyCredit = customer.TransitBuyCredit + transaction.Amount;
                    futureTransitSellCredit = customer.TransitSellCredit + transaction.Amount;
                    futureBuyCredit = customer.BuyCredit + (transaction.Amount * sign);
                    futureSellCredit = customer.SellCredit - (transaction.Amount * sign);
                    futurePosition = customer.Position - (transaction.Amount * sign);
                    margin = preTransactionSummary.AvgSell - preTransactionSummary.AvgBuy;


                    if (preTransactionSummary.TotalBuy > 0 & preTransactionSummary.TotalSell > 0)
                    {
                        if (preTransactionSummary.TotalBuy < preTransactionSummary.TotalSell)
                            minimalAmount = preTransactionSummary.TotalBuy;
                        else
                            minimalAmount = preTransactionSummary.TotalSell;
                    }

                    futureProfit = margin * minimalAmount;

                    if (customer.CustomerType.Equals(CustomerType.OPCF))
                    {

                        sqlCommand = "UPDATE Customer SET transitBuyCredit = @transitBuyCredit, transitSellCredit = @transitSellCredit," + "buyCredit = @buyCredit, sellCredit = @sellCredit, position = @position, profit = @profit," +
                                     "avgBuy = @avgBuy, avgSell = @avgSell WHERE (idCustomer = @idCustomer)";

                        dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                        metroDb.AddInParameter(dbCommand, "@transitBuyCredit", DbType.Currency, futureTransitBuyCredit);
                        metroDb.AddInParameter(dbCommand, "@transitSellCredit", DbType.Currency, futureTransitSellCredit);
                        metroDb.AddInParameter(dbCommand, "@buyCredit", DbType.Currency, futureBuyCredit);
                        metroDb.AddInParameter(dbCommand, "@sellCredit", DbType.Currency, futureSellCredit);
                        metroDb.AddInParameter(dbCommand, "@position", DbType.Currency, futurePosition);
                        metroDb.AddInParameter(dbCommand, "@profit", DbType.Currency, futureProfit);
                        metroDb.AddInParameter(dbCommand, "@avgBuy", DbType.Currency, preTransactionSummary.AvgBuy);
                        metroDb.AddInParameter(dbCommand, "@avgSell", DbType.Currency, preTransactionSummary.AvgSell);
                        metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);

                        metroDb.ExecuteNonQuery(dbCommand);
                    }
                    else if (customer.CustomerType.Equals(CustomerType.Cambiario))
                    {
                        if (transaction.OriginalOrder.OrderBusinessType.Equals(BusinessType.Buy))
                        {
                            sqlCommand = "UPDATE Customer SET transitBuyCredit = @transitBuyCredit, buyCredit = @buyCredit " +
                                         "WHERE (idCustomer = @idCustomer)";
                            dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                            metroDb.AddInParameter(dbCommand, "@transitBuyCredit", DbType.Currency, futureTransitBuyCredit);
                            metroDb.AddInParameter(dbCommand, "@buyCredit", DbType.Currency, futureBuyCredit);
                            metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);
                            metroDb.ExecuteNonQuery(dbCommand);

                        }
                        else if (transaction.OriginalOrder.OrderBusinessType.Equals(BusinessType.Sell))
                        {
                            sqlCommand = "UPDATE Customer SET transitSellCredit = @transitSellCredit, sellCredit = @sellCredit " +
                                         "WHERE (idCustomer = @idCustomer)";
                            dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                            metroDb.AddInParameter(dbCommand, "@transitSellCredit", DbType.Currency, futureTransitSellCredit);
                            metroDb.AddInParameter(dbCommand, "@sellCredit", DbType.Currency, futureSellCredit);
                            metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);
                            metroDb.ExecuteNonQuery(dbCommand);

                        }
                    }

                    sqlCommand = "MakeStats";
                    dbCommand = metroDb.GetStoredProcCommand(sqlCommand);
                    metroDb.AddInParameter(dbCommand, "@market", DbType.String, OrderServices.Market2Str(transaction.OriginalOrder.Market));
                    metroDb.AddInParameter(dbCommand, "@date", DbType.DateTime, DateTime.Today);
                    metroDb.ExecuteNonQuery(dbCommand);

                    scope.Complete();
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<Metro.Admin.BusinessEntities.Transaction> GetTransactionsFromUser(string idUser,DateTime date, string market)
        {
            List<Metro.Admin.BusinessEntities.Transaction> transactions = new List<Metro.Admin.BusinessEntities.Transaction>();
            Metro.Admin.DataAccess.Order orderDA = new Metro.Admin.DataAccess.Order();
            Metro.Admin.BusinessEntities.Transaction transaction = null;
            Metro.Admin.BusinessEntities.Order order = null;

            try
            {
                string sqlCommand = "SELECT [Transaction].id, [Transaction].idOrderPost, [Transaction].idOrderGet, [Transaction].date," + 
                                    "[Transaction].state, [Transaction].amount,[Transaction].basePrice, [Transaction].commission," + 
                                    "[Transaction].tax, [Transaction].baseTax, [Transaction].taxTransaction, [Transaction].type," +
                                    "[Transaction].idPair FROM [Transaction] INNER JOIN [Order] ON [Transaction].idOrderPost = [Order].id " +
                                    "WHERE ([Order].market = @market) AND ([Transaction].date >= @date) AND [Transaction].state='A' ";

                if (!idUser.Equals(String.Empty))
                   sqlCommand += "AND ([Order].idUser = @idUser)";

                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
               
                if (!idUser.Equals(String.Empty))
                {
                    Guid idUserForQuery = new Guid(idUser); 
                    metroDb.AddInParameter(dbCommand, "@idUser", DbType.Guid, idUserForQuery);
                }

                metroDb.AddInParameter(dbCommand, "@date", DbType.DateTime, date);
                metroDb.AddInParameter(dbCommand, "@market", DbType.String, market);
                
                using (IDataReader dataReader = metroDb.ExecuteReader(dbCommand))
                {
                    while (dataReader.Read())
                    {
                        transaction = new Metro.Admin.BusinessEntities.Transaction();

                        transaction.Id = dataReader["id"].ToString();
                        transaction.IdOrderPost = dataReader["idOrderPost"].ToString();
                        order = orderDA.GetOrderById(transaction.IdOrderPost);  

                        transaction.IdOrderGet = dataReader["idOrderGet"].ToString();
                        transaction.IdPair = dataReader["idPair"].ToString();
                        transaction.Date = Convert.ToDateTime(dataReader["date"].ToString());
                        transaction.Commission = Convert.ToDecimal(dataReader["commission"].ToString());
                        transaction.Amount = Convert.ToDecimal(dataReader["amount"].ToString());
                        transaction.BasePrice = Convert.ToDecimal(dataReader["basePrice"].ToString());
                        transaction.BaseTax = Convert.ToDecimal(dataReader["baseTax"].ToString());
                        transaction.Tax = Convert.ToDecimal(dataReader["tax"].ToString());
                        transaction.TaxTransaction = Convert.ToDecimal(dataReader["taxTransaction"].ToString());
                        transaction.Total = transaction.BasePrice * transaction.Amount;
                        transaction.Type = Convert.ToInt32(dataReader["type"].ToString());
                        transaction.OriginalOrder = order; 
                        transaction.TransactionState = OrderServices.Letter2OrderState(dataReader["state"].ToString());   

                        transactions.Add(transaction); 
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return transactions;
        }
    }
}